/**
 * 
 */
package com.zito.manager;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.downlove.common.DateUtils;
import com.downlove.common.RequestContext;
import com.downlove.database.QueryHelper;
import com.downlove.database.SequenceManager;
import com.zito.ErrMsgException;
import com.zito.bean.Invest;
import com.zito.bean.Investans;
import com.zito.bean.Investoption;
import com.zito.bean.Investques;
import com.zito.common.Paginator;
import com.zito.utils.QueryHelperExtend;

/**
 * @author YanJiaXing
 * 
 */
public class InvestManager {
	private Paginator paginator;
	private final Logger logger = LoggerFactory
			.getLogger(InvestManager.class);

	public String getPaginator() {
		return this.paginator.getCssPageBlock();
	}

	public Paginator getPaginatorBean() {
		return this.paginator;
	}
	
	/**
	 * 查询列表
	 * @param pagesize
	 * @return
	 */
	public List<Invest> getInvestListAll(int pagesize) throws Exception {
		String filter = "1=1";
		return SelectInves(filter,pagesize);
	}

	/**
	 * 查询列表
	 * @param pagesize
	 * @return
	 */
	public List<Invest> getInvestList(int pagesize) throws Exception {
		String filter = " status = 1 ";
		return SelectInves(filter,pagesize);
	}
	
	@SuppressWarnings("unchecked")
	public List<Invest> SelectInves(String filter,int pagesize) throws Exception {
		RequestContext rc = RequestContext.get();
		Invest obj = new Invest();
		int total = obj.TotalCount(filter);
		this.paginator = new Paginator(rc, total, pagesize);
		// 设置当前页数和总页数
		int totalpages = this.paginator.getTotalPages();
		int curpage = this.paginator.getCurrentPage();
		if (totalpages == 0) {
			curpage = 1;
			totalpages = 1;
		}
		List<Long> list = obj.idListFilter(filter, curpage, pagesize);
		return obj.LoadList(list);
	}
	
	/**
	 * 根据问卷ID 查询问题列表
	 * @return
	 * @throws Exception
	 */
	public List<Investques> getInvestQuesByMainID(long id) throws Exception {
		String sql = "SELECT * FROM investques WHERE InvestID = ?";
		List<Investques> list = QueryHelper.query(Investques.class, sql, id);
		return list;
	}

	/**
	 * 根据问题ID 查询选项列表
	 * @return
	 * @throws Exception
	 */
	public List<Investoption> getInvestOptionByQuesID(long id) throws Exception {
		String sql = "SELECT * FROM investoption WHERE InvestQuesID=?";
		List<Investoption> list = QueryHelper.query(Investoption.class, sql, id);
		return list;
	}

	/**
	 * 根据问题ID 查询选项列表
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> getInvestAnsByQuesID(long questid) throws Exception {
		String sql = "SELECT A.*" +
						",(SELECT COUNT(*) FROM investans WHERE InvestQuesID ="+questid+" AND AnsContent like concat ('%',A.ID,'%')) AS `choose`" +
						",(SELECT COUNT(*) FROM investans WHERE InvestQuesID ="+questid+" ) AS `all`" +
					" FROM investoption A WHERE InvestQuesID="+questid;
		List<Map<String, Object>> list = QueryHelperExtend.queryListMap(sql);
		return list;
	}
	
	/**
	 * 查询上一篇
	 * @param columnid
	 * @param articleid
	 * @param postdate
	 * @return
	 * @throws Exception
	 */
	public Invest getLastInvest(Long investid,String createtime) throws Exception{
		String sql ="SELECT * FROM Invest WHERE createtime>=? AND ID != ? " +
				"AND status = 1 ORDER BY createtime Limit 1";
		Invest obj = new Invest();
		obj = QueryHelper.read(Invest.class, sql, createtime,investid);
		return obj;
	}
	

	/**
	 * 查询下一篇 
	 * @param columnid
	 * @param articleid
	 * @param postdate
	 * @return
	 * @throws Exception
	 */
	public Invest getNextInvest(Long investid,String createtime) throws Exception{
		String sql ="SELECT * FROM Invest WHERE createtime<=? AND ID != ? " +
				"AND status = 1 ORDER BY createtime DESC Limit 1";
		Invest obj = new Invest();
		obj = QueryHelper.read(Invest.class, sql, createtime,investid);
		return obj;
	}
	
	
	/**
	 * 设置该问卷为使用
	 * @return
	 * @throws Exception
	 */
	public boolean setStatus() throws Exception {
		RequestContext rc = RequestContext.get();
		boolean re = false;
		long id = rc.param("id", -1);
		if (id == -1)
			return re;
		Connection conn = null;
		conn = QueryHelper.getConnection();
		conn.setAutoCommit(false);
		try {
			String Set_sql ="UPDATE invest SET status = 1 WHERE ID = ?";
//			String Un_sql ="UPDATE invest SET status = 0 WHERE ID <> ?";
			QueryHelper.update(conn, Set_sql, id);
//			QueryHelper.update(conn, Un_sql, id);
			re=true;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			re = false;
			conn.rollback();
			throw new Exception(e.getMessage());
		}finally{
			conn.close();
		}
		return re;
	}

	/**
	 * 解除该问卷使用状态
	 * @return
	 * @throws Exception
	 */
	public boolean unStatus() throws Exception {
		RequestContext rc = RequestContext.get();
		boolean re = false;
		long id = rc.param("id", -1);
		if (id == -1)
			return false;
		String Set_sql ="UPDATE invest SET status = 0 WHERE ID = ?";
		re = QueryHelper.update(Set_sql, id)>0;
		return re;
	}
	
	/**
	 * 根据ID查询详细信息
	 * @return
	 */
	public Invest getInvestInfo() throws Exception {
		RequestContext rc = RequestContext.get();
		long id = rc.param("id", -1);
		Invest obj = new Invest();
		obj = obj.Get(id);
		return obj;
	}
	
	/**
	 * 根据问卷ID 查询问题列表
	 * @return
	 * @throws Exception
	 */
	public List<Investques> getInvestQuesList() throws Exception {
		RequestContext rc = RequestContext.get();
		long id = rc.param("id", -1);
		String sql = "SELECT * FROM investques WHERE (InvestID = ? AND InvestID <>-1) OR (InvestID = -1 AND identifies = ?)";
		List<Investques> list = QueryHelper.query(Investques.class, sql, id,rc.session().getId());
		return list;
	}

	/**
	 * 根据问题ID 查询选项列表
	 * @return
	 * @throws Exception
	 */
	public List<Investoption> getInvestOptionList() throws Exception {
		RequestContext rc = RequestContext.get();
		long id = rc.param("id", -1);
		String sql = "SELECT * FROM investoption WHERE InvestQuesID=?";
		List<Investoption> list = QueryHelper.query(Investoption.class, sql, id);
		return list;
	}
	
	/**
	 * 用户问卷提交
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public boolean submitInvest() throws Exception {
		RequestContext rc = RequestContext.get();
		boolean re = false;
		String AnsJson = rc.param("AnsJson", "");
		Long MainID = rc.param("MainID", -1L);
		JSONArray itemArray = JSONArray.fromObject(AnsJson);
		List<Investans> list = (List<Investans>) JSONArray
				.toCollection(itemArray, Investans.class);
		Connection conn = null;
		conn = QueryHelper.getConnection();
		conn.setAutoCommit(false);
		try {
			for (Investans temp : list) {
				String error = temp.doValidator();
				if (!StringUtils.isBlank(error))
					throw new ErrMsgException(error);
				temp.setId(SequenceManager.nextID(Investans.class.getSimpleName()));
				temp.setAnsname(InteractSession.get().getUserName());
				temp.setAnstime(DateUtils.getDateTime());
				temp.setAnsip(rc.ip());
				temp.Save(conn);
			}
			String sql ="UPDATE invest SET takeinnum = takeinnum+1 WHERE ID = ?";
			QueryHelper.update(conn, sql, MainID);
			re=true;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			re = false;
			conn.rollback();
			throw new Exception(e.getMessage());
		}finally{
			conn.close();
		}
		return re;
	}
	
	/**
	 * 保存问卷
	 * @return
	 */
	public Long saveInvest() throws Exception {
		RequestContext rc = RequestContext.get();
		long tempid = rc.param("id", -1);
		return null == new Invest().Get(tempid) ? insertInvest(rc)
				: editInvest(rc);
	}
	
	/**
	 * 添加问卷
	 * 
	 * @param rc
	 * @return
	 * @throws Exception
	 */
	private Long insertInvest(RequestContext rc) throws Exception {
		Invest obj = rc.form(Invest.class);
		String error = obj.doValidator();
		if (!StringUtils.isBlank(error))
			throw new ErrMsgException(error);
		obj.setId(SequenceManager.nextID(Invest.class.getSimpleName()));
		obj.setCreateuserid(MasterSession.get().getUserID());
		obj.setCreateusername(MasterSession.get().getUserName());
		obj.setCreatetime(DateUtils.getDateTime());
		return obj.Save(RequestContext.get().session().getId());
	}
	
	/**
	 * 编辑问卷
	 * 
	 * @param rc
	 * @return
	 * @throws Exception
	 */
	private Long editInvest(RequestContext rc) throws Exception {
		Invest obj = rc.form(Invest.class);
		String error = obj.doValidator();
		if (!StringUtils.isBlank(error))
			throw new ErrMsgException(error);
		return obj.Update(RequestContext.get().session().getId());
	}
	
	/**
	 * 保存问卷问题
	 * @return
	 */
	public boolean saveInvestQues() throws Exception {
		RequestContext rc = RequestContext.get();
		long tempid = rc.param("id", -1);
		return null == new Investques().Get(tempid) ? insertInvestQues(rc)
				: editInvestQues(rc);
	}
	
	/**
	 * 添加问卷问题
	 * 
	 * @param rc
	 * @return
	 * @throws Exception
	 */
	private boolean insertInvestQues(RequestContext rc) throws Exception {
		Investques obj = rc.form(Investques.class);
		Long investid = rc.param("investid", -1L);
		String error = obj.doValidator();
		if (!StringUtils.isBlank(error))
			throw new ErrMsgException(error);
		obj.setInvestid(investid);
		obj.setId(SequenceManager.nextID(Investques.class.getSimpleName()));
		obj.setIdentifies(rc.session().getId());// 临时附值
		return obj.Save()>0;
	}
	
	/**
	 * 编辑问卷问题
	 * 
	 * @param rc
	 * @return
	 * @throws Exception
	 */
	private boolean editInvestQues(RequestContext rc) throws Exception {
		Investques obj = rc.form(Investques.class);
		boolean re = false;
		String error = obj.doValidator();
		if (!StringUtils.isBlank(error))
			throw new ErrMsgException(error);
		String sql ="UPDATE investques SET `Content` = ?,`Type` =?,`identifies`=? WHERE ID=?";
		re = QueryHelper.update(sql, obj.getContent(),obj.getType(),rc.session().getId(),obj.getId())>0;
		return re;
	}
	
	/**
	 * 保存问题选项
	 * @return
	 */
	public boolean saveInvestOption() throws Exception {
		RequestContext rc = RequestContext.get();
		long tempid = rc.param("id", -1);
		return null == new Investoption().Get(tempid) ? insertInvestOption(rc)
				: editInvestOption(rc);
	}
	
	/**
	 * 添加问题选项
	 * 
	 * @param rc
	 * @return
	 * @throws Exception
	 */
	private boolean insertInvestOption(RequestContext rc) throws Exception {
		Investoption obj = rc.form(Investoption.class);
		Long investquesid = rc.param("investquesid", -1L);
		String error = obj.doValidator();
		if (!StringUtils.isBlank(error))
			throw new ErrMsgException(error);
		obj.setInvestquesid(investquesid);
		obj.setId(SequenceManager.nextID(Investoption.class.getSimpleName()));
		return obj.Save()>0;
	}
	
	/**
	 * 编辑问题选项
	 * 
	 * @param rc
	 * @return
	 * @throws Exception
	 */
	private boolean editInvestOption(RequestContext rc) throws Exception {
		Investoption obj = rc.form(Investoption.class);
		boolean re = false;
		String error = obj.doValidator();
		if (!StringUtils.isBlank(error))
			throw new ErrMsgException(error);
		String sql ="UPDATE Investoption SET `Content` = ? WHERE ID=?";
		re = QueryHelper.update(sql, obj.getContent(),obj.getId())>0;
		return re;
	}
	
	/**
	 * 删除问卷
	 * @return
	 * @throws ErrMsgException
	 */
	public boolean deleteInvest() throws Exception {
		RequestContext rc = RequestContext.get();
		boolean re = false;
		long tempid = rc.param("id", -1);
		Connection conn = null;
		conn = QueryHelper.getConnection();
		conn.setAutoCommit(false);
		try {
			String Del_option = "DELETE FROM investoption WHERE InvestQuesID IN " 
						+ "(SELECT ID FROM investques WHERE InvestID = ?)";
			String Del_ques = "DELETE FROM investques WHERE InvestID = ?";
			String Del_invest = "DELETE FROM invest WHERE ID = ?";
			QueryHelper.update(conn, Del_option, tempid);
			QueryHelper.update(conn, Del_ques, tempid);
			QueryHelper.update(conn, Del_invest, tempid);
			re=true;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			re = false;
			conn.rollback();
			throw new Exception(e.getMessage());
		}finally{
			conn.close();
		}
		return re;
	}
	
	/**
	 * 删除问卷问题
	 * @return
	 * @throws ErrMsgException
	 */
	public boolean deleteInvestQues() throws Exception {
		RequestContext rc = RequestContext.get();
		boolean re = false;
		long tempid = rc.param("id", -1);
		Connection conn = null;
		conn = QueryHelper.getConnection();
		conn.setAutoCommit(false);
		try {
			String Del_ques ="DELETE FROM investques WHERE ID = ?";
			String Del_option ="DELETE FROM investoption WHERE InvestQuesID = ?";
			QueryHelper.update(conn, Del_ques, tempid);
			QueryHelper.update(conn, Del_option, tempid);
			re=true;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			re = false;
			conn.rollback();
			throw new Exception(e.getMessage());
		}finally{
			conn.close();
		}
		return re;
	}
	
	/**
	 * 删除问卷问题
	 * @return
	 * @throws ErrMsgException
	 */
	public boolean deleteInvestOption() throws Exception {
		RequestContext rc = RequestContext.get();
		long tempid = rc.param("id", -1);
		String Del_option ="DELETE FROM investoption WHERE ID = ?";
		boolean re = QueryHelper.update(Del_option, tempid)>0;
		return re;
	}
}
